const { execSQL } = require("../db/mysql");
const { hotel_room_card } = require("../entity");
//根据时间段获取酒店插口取电次数和无效次数
async function insert_card_for_power(leftTime, rightTime) {//从数据库拿取插卡取电是否成功数据
    // hotelRoomData=hotel_room_card;//本地//获取酒店id、酒店名和房间对应register
    var sql = `SELECT registerId,MAX(CASE STATUS WHEN 0 THEN acount ELSE 0 END ) invalid,SUM(acount) sumcount FROM (
        SELECT registerId,STATUS,COUNT(*) acount FROM rest_net_using_inspection WHERE reqTime>'${leftTime}' AND resTime<'${rightTime}'  GROUP BY registerId,STATUS
        ) rnui GROUP BY registerId`//以防万一，排序
    // console.log(sql);
    return Promise.all([execSQL(sql), getHotelRoomData()]).then(result => {//listData插卡取电信息
        var arrs = [], listData = result[0], hotelRoomData = result[1];
        for (var i = 0; i < listData.length; i++) {
            for (var key in hotelRoomData) {//hotelRoomData.length，只遍历1，2，3，6，7，8
                if (listData[i].registerId in hotelRoomData[key]) {
                    arrs.push({
                        'registerId': listData[i].registerId,
                        'hotelname': hotelRoomData[key].hotelname,
                        'roomname': hotelRoomData[key][listData[i].registerId],
                        'invalid': listData[i].invalid || 0,
                        'sumcount': listData[i].sumcount
                    });
                    break;
                }
            }
        }
        // console.log(arrs) 
        return arrs;//使数据按某种规范格式输出
    }).catch((err) => console.log("insert_key_power", err));
}//返回registerId: 92, sumcount: 144, invalid: 93
// insert_card_for_power('2022-07-04 21:02','2022-07-05 21:04');
function get_hotel_daily_settel(leftTime, rightTime) {//根据时间段查询酒店日租信息
    var monitor = 'hotelId,name,roomnum,hds.from,hds.desc,amount,isSettled,isNotified';
    var sql = `select ${monitor} from hotel_daily_settlement hds inner join (
        SELECT hotel.id,hotel.name,COUNT(*) roomnum FROM hotel INNER JOIN hotel_room ON hotel.id=hotel_room.hotelId where hotel.id<>9 GROUP BY hotel.id
        ) h on hds.hotelId=h.id`;//获取两张表所有数据，华飞物联众客户Id=9，需不需要剔除
    sql += ` where hds.from>'${leftTime}' and hds.from<'${rightTime}'`;//数据量巨大，加时间限制
    sql += ` order by hotelId`;//筛选酒店加排序 and hotelId in (1,2,3,6,7,8)
    // console.log(sql);
    return execSQL(sql).then((data) => {
        var arrs = [], arrs2 = [], index = 0;
        for (var i = 0; i < data.length; i++) {
            var lived = parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
            arrs.push({
                amount: data[i].amount,
                from: data[i].from.getFullYear() + "-" + (data[i].from.getMonth() + 1) + "-" + data[i].from.getDate(),
                isSettled: data[i].isSettled,
                name: data[i].name,
                number: lived
            });
            if (data[i].hotelId != index) {
                index = data[i].hotelId;
                arrs2.push({//一条记录时情况
                    'key': data[i].hotelId,
                    'name': data[i].name,
                    'hotelRoomNumber': data[i].roomnum,
                    'livedNumberInTime': lived,
                    'hotelTotalRoomNumberInTime': data[i].roomnum,
                    'livedRateInTime': lived / data[i].roomnum,
                    'costInTime': data[i].amount,
                    'lastIncomeInTime': data[i].amount,
                    'getIncomeInTime': (data[i].isSettled === 1) ? data[i].amount : 0,
                    'notGetIncomeInTime': (data[i].isSettled === 0) ? data[i].amount : 0
                })
            } else {//多天情况
                arrs2[arrs2.length - 1].livedNumberInTime += lived;
                arrs2[arrs2.length - 1].hotelTotalRoomNumberInTime += data[i].roomnum;
                arrs2[arrs2.length - 1].livedRateInTime = arrs2[arrs2.length - 1].livedNumberInTime / arrs2[arrs2.length - 1].hotelTotalRoomNumberInTime;
                arrs2[arrs2.length - 1].costInTime += data[i].amount;
                arrs2[arrs2.length - 1].lastIncomeInTime += data[i].amount;
                if (data[i].isSettled === 1) arrs2[arrs2.length - 1].getIncomeInTime += data[i].amount;
                else arrs2[arrs2.length - 1].notGetIncomeInTime += data[i].amount;
            }
        }
        return [arrs, arrs2];
    });
}
// get_hotel_daily_settel('2022-1-16 00:00','2022-8-8 00:00').then(data=>console.log(data));
function getHotelRoomData(hotel) {//获取所有酒店id，酒店名，房间名，房间对应的registerId，耗时4S，月更新。或根据酒店名Id查找
    var sql1 = `SELECT customerId,NAME roomname,registerId,rest_netId FROM \`case\` INNER JOIN (
        SELECT caseId,rnur.id registerId FROM case_node cn INNER JOIN rest_net_using_register rnur ON cn.ieeeAddr=rnur.rest_nodeIeeeAddr
        ) cr ON case.id=cr.caseId order by customerId`;//roomname,customerId,registerId

    var sql2 = `SELECT hotel.caseCustomerId customerId,hr.name roomname,hotel.id,hotel.name hotelname FROM hotel INNER JOIN hotel_room hr ON hotel.id=hr.hotelId`;//caseCustomerId,roomname,hotelId、name
    if (hotel != null && hotel != -1) sql2 += ` WHERE hotel.id IN (${hotel}) order by customerId`;
    else sql2 += ` order by customerId`;
    return Promise.all([execSQL(sql1), execSQL(sql2)]).then(([data1, data2]) => {
        var arrs = {}, index1 = 0, index2 = 0;
        // console.log("1",sql2)
        for (var i = 0; i < data1.length; i++) {//data2中44号存在数据，45号不存在
            while (data1[i].customerId > data2[index1].customerId) {
                if (++index1 >= data2.length) return arrs;
            }
            if (data1[i].customerId < data2[index1].customerId) continue;
            else {
                index2 = index1;
                while (true) {
                    if (data1[i].roomname != data2[index2].roomname) {
                        if (++index2 >= data2.length || data1[i].customerId != data2[index2].customerId) break;
                    } else if (data1[i].roomname == data2[index2].roomname) {
                        if (arrs[data2[index2].id]) {
                            arrs[data2[index2].id][data1[i].registerId] = {
                                'roomname': data1[i].roomname,
                                'rest_netId': data1[i].rest_netId
                            }
                            break;
                        } else {
                            arrs[data2[index2].id] = {
                                'hotelname': data2[index2].hotelname
                            }
                        }
                    }
                }
            }
        }
        return arrs;//{hotelId:{'hotelname':,'reId':{roomname,rest_netId}...}
    });
}
// getHotelRoomData()//.then(data=>console.log(data['3']));
var getHotel_RegisterId = function (registerId) {//根据registerId获取酒店名
    var sql = `select rest_nodeIeeeAddr from rest_net_using_register where id=${registerId}`;
    sql = `select caseId from case_node WHERE ieeeAddr=(${sql})`
    sql = `select customerId from \`case\` WHERE id=(${sql})`
    sql = `select name from case_customer WHERE id=(${sql})`
    // console.log(sql);
    return execSQL(sql).then((data) => {
        // console.log("typeof data",data);
        return data;
    }).catch((err) => console.log("controlDB.jsError", err));
}
// getHotel_RegisterId(1);
// getroomNum();
function getRegisterIdOfHotelList(hotelName, leftTime, rightTime) {//根据酒店名时间段获取酒店插卡取电详情
    var sql = `select caseCustomerId from hotel where name='${hotelName}'`;
    sql = `select id from \`case\` where customerId=(${sql})`;
    sql = `SELECT ieeeAddr FROM case_node c_n INNER JOIN (${sql}) c ON c_n.caseId=c.id WHERE NAME='插卡取电'`;
    sql = `SELECT id FROM rest_net_using_register rnur INNER JOIN (${sql}) iee ON rnur.rest_nodeIeeeAddr=iee.ieeeAddr`;//获取酒店所有registerId，数量为酒店房数
    sql = `SELECT * FROM rest_net_using_inspection rnui INNER JOIN (${sql}) rnur ON rnui.registerId=rnur.id`;//获取该酒店插卡取电所有数据
    sql += ` where reqTime>'${leftTime}' and resTime<'${rightTime}'`;//数据量巨大，加时间限制
    // console.log(sql)
    return execSQL(sql).then((data) => {
        // console.log(data)
        return data;
    });
}
// getRegisterIdOfHotelList('井冈之星','2022-07-04 21:02','2022-07-05 21:04');
function getCardListdOfHotel(hotel) {//根据酒店名或Id获取所有取电卡
    var sql;
    if (typeof hotel === 'string') sql = `(select caseCustomerId from hotel where name='${hotel}')`;
    else sql = `(SELECT caseCustomerId FROM hotel WHERE id=${hotel})`;
    sql = `select id from \`case\` where customerId=${sql}`;
    sql = `SELECT ieeeAddr FROM case_node c_n INNER JOIN (${sql}) c ON c_n.caseId=c.id`;
    sql = `SELECT id registerId FROM rest_net_using_register rnur INNER JOIN (${sql}) iee ON rnur.rest_nodeIeeeAddr=iee.ieeeAddr`;//获取酒店所有registerId，数量为酒店房数
    // console.log('getCardListdOfHotel:',sql)
    return execSQL(sql).then((data) => {
        // console.log(data)
        return data;
    });
}
// getCardListdOfHotel(7);
function getroomNum() {//查询酒店id、酒店名和房间数量，返回hotelId\name\roomnum
    var sql = `select id,name from hotel where id in (1,2,3,6,7,8)`;
    sql = `SELECT hotelId,h.name,COUNT(hotelId) roomnum FROM hotel_room hr INNER JOIN (${sql}) h ON hr.hotelId=h.id GROUP BY hotelId`;
    // console.log(sql)
    return execSQL(sql).then((data) => {
        // console.log(data);
        return data;
    }).catch((err) => console.log("control.jsError:", err));//
}
module.exports = {
    insert_card_for_power,
    getHotel_RegisterId,
    getRegisterIdOfHotelList,
    getCardListdOfHotel,
    get_hotel_daily_settel,
    getHotelRoomData
}